-- EXEC SP_RELEASE_SCREENS_REPORT '0','SORT','0','0','ATK4','CLOSED,APPROVED,CANCELLED','0' 
ALTER  PROC SP_RELEASE_SCREENS_REPORT
 	@STEP_ID VARCHAR (500),
	@P_TYPE VARCHAR (50),
	@DATE_FROM VARCHAR (50),
	@DATE_TO VARCHAR (50),
	@LOCATION_FROM VARCHAR(300),
	@LOT_STATUS VARCHAR(100),
 	@DEFAULT_PROMPT VARCHAR(50)
AS
BEGIN
	DECLARE @RESULTS TABLE (LOT_ID INT, LOT_NUMBER VARCHAR(50), PROCESSSTEP_ID INT, PART_NUMBER VARCHAR(50),PS_TYPE VARCHAR(50),Q_AVAILABLE INT,Q_PULLED INT,LOC_PREV VARCHAR(50),REL_DATE VARCHAR(100),
				LOT_WAFER_ID VARCHAR(100),PS_TYPE_ID INT,STATUS VARCHAR (50),REL_NUMBER VARCHAR(50),PO_RELEASE VARCHAR(50),REV_RELEASE VARCHAR(50),PRODUCT_DESCRIPTION VARCHAR(200), QTY_OUT BIGINT, DATE_OUT DATETIME, BIN_INFO VARCHAR(50) )
	DECLARE @PROCESS_STEPS_TABLE TABLE( PROCESSSTEP_ID BIGINT )
		
	DECLARE @RESULTS_TABLE TABLE(
		LOT_ID INT, LOT_NUMBER VARCHAR(50), PROCESSSTEP_ID INT, PART_NUMBER VARCHAR(50), PS_TYPE_ID INT, PS_TYPE VARCHAR(50), 
		Q_AVAILABLE INT, Q_PULLED INT, LOC_PREV VARCHAR(50), REL_DATE VARCHAR(100), LOT_WAFER_ID VARCHAR(100), STATUS VARCHAR (50), 
		REL_NUMBER VARCHAR(50), PO_RELEASE VARCHAR(50), REV_RELEASE VARCHAR(50),PRODUCT_DESCRIPTION VARCHAR(200), QTY_OUT BIGINT, DATE_OUT DATETIME, BIN_INFO VARCHAR(50) )

	DECLARE @LOT_NUMBER VARCHAR(50)
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @Q_AVAILABLE INT
	DECLARE @Q_PULLED INT
	DECLARE @LOC_PREV VARCHAR(50)
	DECLARE @REL_DATE VARCHAR(50)
	DECLARE @LOT_WAFER_ID VARCHAR(300)
	DECLARE @PS_TYPE_ID INT
	DECLARE @STATUS VARCHAR(50)
	DECLARE @REL_NUMBER VARCHAR(50)
	DECLARE @PO_RELEASE VARCHAR(50)
	DECLARE @REV_RELEASE VARCHAR(50)
	DECLARE @FINAL_WAFER_ID VARCHAR(100)

	DECLARE @PRODUCT_DESCRIPTION VARCHAR(200)
	DECLARE @SORT_LOT_WAFER_ID VARCHAR(300)
	DECLARE @SORT_WAFERS_COUNT INT
	DECLARE @SORT_REL_WAFERS_COUNT INT
	DECLARE @LOT_ID INT
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @GOOD_DIE_QTY BIGINT
	DECLARE @SORT_DATE_OUT DATETIME
	DECLARE @ASSM_QTY_OUT BIGINT
	DECLARE @ASSM_DATE_OUT DATETIME

	DECLARE @REL_DATE_FROM DATETIME
	DECLARE	@REL_DATE_TO DATETIME
	
	DECLARE @MAX_REL_DATE DATETIME
	DECLARE	@MIN_REL_DATE DATETIME

	DECLARE @CHILDCURSOR CURSOR
	DECLARE @RELEASECURSOR CURSOR
	DECLARE @STATUS_TABLE TABLE ( STATUS VARCHAR(50))
	DECLARE @VENDOR_TABLE TABLE ( VENDOR_ID INT)
	DECLARE @WAFER_IDS_TABLE TABLE (WAFER_ID INT)
	DECLARE @BIN_INFO VARCHAR(50)
	
	IF @LOCATION_FROM = '0'
	BEGIN
		INSERT INTO @VENDOR_TABLE (VENDOR_ID) SELECT DISTINCT VENDOR_ID FROM VENDOR 
	END
	ELSE
	BEGIN
		INSERT INTO @VENDOR_TABLE (VENDOR_ID) SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @LOCATION_FROM )
	END

	IF( LTRIM(RTRIM(@P_TYPE))  = 'SORT' )
	BEGIN
		SELECT @MAX_REL_DATE = MAX( CONVERT( DATETIME, SORT_REL_DATE )) FROM SORT_RELEASE_TABLE
		SELECT @MIN_REL_DATE = MIN( CONVERT( DATETIME, SORT_REL_DATE )) FROM SORT_RELEASE_TABLE
		IF @LOT_STATUS = '0'
		BEGIN
			INSERT INTO @STATUS_TABLE (STATUS) SELECT DISTINCT STATUS FROM SORT_RELEASE_TABLE
			
		END
		ELSE
		BEGIN
			INSERT INTO @STATUS_TABLE (STATUS) SELECT * FROM FN_GET_STRINGS_IN_STRING(@LOT_STATUS)
		END
	END
	ELSE
	BEGIN
		SELECT @MAX_REL_DATE = MAX( CONVERT( DATETIME, ASSEMBLY_RELEASE_DATE )) FROM ASSM_RELEASE_TABLE
		SELECT @MIN_REL_DATE = MIN( CONVERT( DATETIME, ASSEMBLY_RELEASE_DATE )) FROM ASSM_RELEASE_TABLE
		IF @LOT_STATUS = '0'
		BEGIN
			INSERT INTO @STATUS_TABLE (STATUS) SELECT DISTINCT STATUS FROM ASSM_RELEASE_TABLE 
		END
		ELSE
		BEGIN
			INSERT INTO @STATUS_TABLE (STATUS) SELECT * FROM FN_GET_STRINGS_IN_STRING(@LOT_STATUS)
		END
	END
	
	IF ( @DATE_FROM = '0') AND (@DATE_TO = '0') 
	BEGIN
		SET @REL_DATE_TO = @MAX_REL_DATE
		SET @REL_DATE_FROM = @MIN_REL_DATE
	END
	ELSE IF (@DATE_FROM <> '0') AND (@DATE_TO <> '0') 
	BEGIN
		SET @REL_DATE_TO = @DATE_TO
		SET @REL_DATE_FROM = @DATE_FROM
	END	
	ELSE IF (@DATE_FROM = '0') AND (@DATE_TO <> '0') 
	BEGIN
		SET @REL_DATE_TO = @DATE_TO
		SET @REL_DATE_FROM = @MIN_REL_DATE
	END 
 	ELSE IF (@DATE_FROM <> '0') AND (@DATE_TO = '0') 
	BEGIN
		SET @REL_DATE_TO = @MAX_REL_DATE
		SET @REL_DATE_FROM = @DATE_FROM	
	END
	ELSE
	BEGIN
		SET @REL_DATE_TO = @MAX_REL_DATE
		SET @REL_DATE_FROM = @MIN_REL_DATE
	END
	
	--DECLARE @TEMPRESULTS TABLE (LOT_NUMBER VARCHAR(50),PART_NUMBER VARCHAR(50),PS_TYPE VARCHAR(50),Q_AVAILABLE INT,Q_PULLED INT,LOC_PREV VARCHAR(50),REL_DATE VARCHAR(100),
				--LOT_WAFER_ID VARCHAR(100),STATUS VARCHAR (50),REL_NUMBER VARCHAR(50),PO_RELEASE VARCHAR(50),REV_RELEASE VARCHAR(50) )


	IF(@STEP_ID <> '0')
	BEGIN
		INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID )  SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @STEP_ID )
	END
	ELSE IF (@STEP_ID = '0')
	BEGIN
		IF( @P_TYPE <> '0' )
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE_NAME = @P_TYPE  --AND P_PARENT_PRODUCT_ID IS NULL 
		END
		ELSE IF( @P_TYPE = '0' )
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  --AND P_PARENT_PRODUCT_ID IS NULL
		END
	END
	
	IF( LTRIM(RTRIM(@P_TYPE))  = 'SORT' )
	BEGIN
		INSERT INTO @RESULTS_TABLE 
		SELECT SRLT.LOT_ID, SRLT.LOT_NUMBER, PS.PROCESSSTEP_ID, PRO.PART_NUMBER, 5, PS.PS_TYPE_NAME, SUM(SRLT.QTY_AVAIL) AS QTY_AVAIL, 
		SUM(SRLT.QTY_PULLED) AS QTY_PULLED, VEN.V_NAME, SRT.SORT_REL_DATE, SRLT.WAFER_ID, SRT.STATUS, SRT.SORT_REL_NUMB, SRT.SORT_REL_PO, SRT.SORT_REVISION,CONVERT(VARCHAR , PRO.P_COMMENTS), NULL, NULL, NULL
		FROM SORT_RELEASE_LOT_TABLE SRLT , PRODUCT PRO , PROCESS_STEP PS, VENDOR VEN, SORT_RELEASE_TABLE SRT
		WHERE SRLT.SORT_REL_ID = SRT.SORT_REL_ID AND SRT.PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID IN( SELECT PROCESSSTEP_ID FROM @PROCESS_STEPS_TABLE ) 
		AND SRT.VEND_ID = VEN.VENDOR_ID AND SRT.SORT_REL_DATE <= CONVERT(DATETIME,@REL_DATE_TO) AND SRT.SORT_REL_DATE >= CONVERT(DATETIME,@REL_DATE_FROM) AND VEN.VENDOR_ID IN (SELECT VENDOR_ID FROM @VENDOR_TABLE) AND STATUS IN ( SELECT STATUS FROM @STATUS_TABLE )
		GROUP BY PS.PS_TYPE_NAME, PRO.PART_NUMBER, PS.PROCESSSTEP_ID, SRT.SORT_REL_NUMB, SRLT.LOT_ID, SRLT.LOT_NUMBER, VEN.V_NAME,
		SRT.SORT_REL_DATE, SRLT.WAFER_ID, SRT.STATUS, SRT.SORT_REL_NUMB, SRT.SORT_REL_PO, SRT.SORT_REVISION,CONVERT(VARCHAR , PRO.P_COMMENTS)
	END
	ELSE IF( LTRIM(RTRIM(@P_TYPE))  = 'ASSEMBLY' )
	BEGIN		
		INSERT INTO @RESULTS_TABLE  
		SELECT ARLT.LOT_ID, ART.LOT_NUMBER, PS.PROCESSSTEP_ID, PRO.PART_NUMBER, 3, PS.PS_TYPE_NAME, NULL, SUM(QTY_PULLED) AS QTY_PULLED, 
		VEN.V_NAME, ART.ASSEMBLY_RELEASE_DATE, NULL, ART.STATUS, ASSEMBLY_RELEASE_NO, PO_NUMBER, ASSM_REVESION_NUMBER,CONVERT(VARCHAR , PRO.P_COMMENTS), NULL, NULL, REPLACE( ART.BIN_INFO, '&', ';')
		FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART, PRODUCT PRO, PROCESS_STEP PS, VENDOR VEN 
		WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID AND ART.PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND 
		PRO.PROCESSSTEP_ID IN( SELECT PROCESSSTEP_ID FROM @PROCESS_STEPS_TABLE ) AND ART.TO_VENDOR_ID = VEN.VENDOR_ID AND ART.ASSEMBLY_RELEASE_DATE <= CONVERT(DATETIME,@REL_DATE_TO) AND ART.ASSEMBLY_RELEASE_DATE >= CONVERT(DATETIME,@REL_DATE_FROM) AND
		VEN.VENDOR_ID IN (SELECT VENDOR_ID FROM @VENDOR_TABLE) AND STATUS IN ( SELECT STATUS FROM @STATUS_TABLE ) 
		GROUP BY PS.PS_TYPE_NAME, PRO.PART_NUMBER, PS.PROCESSSTEP_ID, ASSEMBLY_RELEASE_NO, ARLT.LOT_ID, ART.LOT_NUMBER, VEN.V_NAME,
		ART.ASSEMBLY_RELEASE_DATE, ART.STATUS, ASSEMBLY_RELEASE_NO, PO_NUMBER, ASSM_REVESION_NUMBER,CONVERT(VARCHAR , PRO.P_COMMENTS),ART.BIN_INFO
	END
	ELSE
	BEGIN	
		INSERT INTO @RESULTS_TABLE 
		SELECT SRLT.LOT_ID, SRLT.LOT_NUMBER, PS.PROCESSSTEP_ID, PRO.PART_NUMBER, 5, PS.PS_TYPE_NAME, SUM(SRLT.QTY_AVAIL) AS QTY_AVAIL, 
		SUM(SRLT.QTY_PULLED) AS QTY_PULLED, VEN.V_NAME, SRT.SORT_REL_DATE, SRLT.WAFER_ID, SRT.STATUS, SRT.SORT_REL_NUMB, SRT.SORT_REL_PO, SRT.SORT_REVISION,CONVERT(VARCHAR , PRO.P_COMMENTS), NULL, NULL, NULL
		FROM SORT_RELEASE_LOT_TABLE SRLT , PRODUCT PRO , PROCESS_STEP PS, VENDOR VEN, SORT_RELEASE_TABLE SRT
		WHERE SRLT.SORT_REL_ID = SRT.SORT_REL_ID AND SRT.PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID IN( SELECT PROCESSSTEP_ID FROM @PROCESS_STEPS_TABLE ) 
		AND SRT.VEND_ID = VEN.VENDOR_ID AND SRT.SORT_REL_DATE <= CONVERT(DATETIME,@REL_DATE_TO) AND SRT.SORT_REL_DATE >= CONVERT(DATETIME,@REL_DATE_FROM) AND VEN.VENDOR_ID IN (SELECT VENDOR_ID FROM @VENDOR_TABLE) AND STATUS IN ( SELECT STATUS FROM @STATUS_TABLE )
		GROUP BY PS.PS_TYPE_NAME, PRO.PART_NUMBER, PS.PROCESSSTEP_ID, SRT.SORT_REL_NUMB, SRLT.LOT_ID, SRLT.LOT_NUMBER, VEN.V_NAME,
		SRT.SORT_REL_DATE, SRLT.WAFER_ID, SRT.STATUS, SRT.SORT_REL_NUMB, SRT.SORT_REL_PO, SRT.SORT_REVISION,CONVERT(VARCHAR , PRO.P_COMMENTS)
	END

	--SELECT * FROM @RESULTS_TABLE

	IF( LTRIM(RTRIM(@P_TYPE))  = 'SORT' )
	BEGIN
		INSERT INTO @RESULTS 
		SELECT LOT_ID, LOT_NUMBER, PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, Q_AVAILABLE, Q_PULLED, LOC_PREV, REL_DATE, LOT_WAFER_ID, PS_TYPE_ID, UPPER(STATUS), REL_NUMBER, PO_RELEASE, REV_RELEASE,PRODUCT_DESCRIPTION, QTY_OUT, DATE_OUT, BIN_INFO FROM @RESULTS_TABLE

		SET @RELEASECURSOR = CURSOR FOR SELECT LOT_ID, LOT_NUMBER, PROCESSSTEP_ID, REL_NUMBER, LOT_WAFER_ID FROM @RESULTS WHERE STATUS = 'CLOSED'
		OPEN @RELEASECURSOR
		FETCH NEXT FROM @RELEASECURSOR INTO @LOT_ID, @LOT_NUMBER, @PROCESSSTEP_ID, @REL_NUMBER, @LOT_WAFER_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT TOP 1 @SORT_DATE_OUT = CONVERT(DATETIME , ISNULL( SL_DATE_OUT, SLH.TIMESTAMP ) ) FROM SORT_LOT_HISTORY SLH, LOT_TABLE LT 
			WHERE SLH.LOT_ID = LT.LOT_ID AND LT.LOT_NUMBER = @LOT_NUMBER AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID ORDER BY SLH.TIMESTAMP DESC
			
			SET @SORT_LOT_WAFER_ID = REPLACE( @LOT_WAFER_ID, ';', ',' ) 

			DELETE FROM @WAFER_IDS_TABLE	
			INSERT INTO @WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @SORT_LOT_WAFER_ID )
			SELECT @SORT_REL_WAFERS_COUNT = COUNT(*) FROM @WAFER_IDS_TABLE
			
			SELECT @SORT_WAFERS_COUNT = ISNULL( COUNT( WAFER_ID ), 0 ), @GOOD_DIE_QTY = ISNULL(SUM( GOOD_DICE ),0) FROM INFORMATIVE_BINNING IB, LOT_TABLE LT
			WHERE IB.LOT_ID = LT.LOT_ID AND LT.LOT_NUMBER = @LOT_NUMBER AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND WAFER_ID IN ( SELECT WAFER_ID FROM @WAFER_IDS_TABLE  )

			IF( @SORT_WAFERS_COUNT > 0 /*AND ( @SORT_WAFERS_COUNT = @SORT_REL_WAFERS_COUNT )*/ )
			BEGIN
				UPDATE @RESULTS SET QTY_OUT = @GOOD_DIE_QTY, DATE_OUT = CONVERT(DATETIME,@SORT_DATE_OUT) WHERE REL_NUMBER = @REL_NUMBER AND LOT_NUMBER = @LOT_NUMBER
			END
		
			SET @LOT_ID = NULL
			SET @REL_NUMBER = NULL
			SET @LOT_WAFER_ID = NULL
			SET @SORT_REL_WAFERS_COUNT = NULL
			SET @SORT_WAFERS_COUNT = NULL
			SET @LOT_NUMBER = NULL
			SET @PROCESSSTEP_ID = NULL
			SET @GOOD_DIE_QTY =  NULL
			SET @SORT_DATE_OUT = NULL

			FETCH NEXT FROM @RELEASECURSOR INTO @LOT_ID, @LOT_NUMBER, @PROCESSSTEP_ID, @REL_NUMBER, @LOT_WAFER_ID
	 	END
	  	CLOSE @RELEASECURSOR
	 	DEALLOCATE @RELEASECURSOR
	
		SELECT * FROM @RESULTS ORDER BY PS_TYPE_ID,REL_NUMBER,REL_DATE DESC
	END
	ELSE
	BEGIN
		INSERT INTO @RESULTS 
		SELECT LOT_ID, LOT_NUMBER, PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, Q_AVAILABLE, Q_PULLED, LOC_PREV, REL_DATE, LOT_WAFER_ID, PS_TYPE_ID, UPPER(STATUS), REL_NUMBER, PO_RELEASE, REV_RELEASE,PRODUCT_DESCRIPTION, QTY_OUT, DATE_OUT, BIN_INFO FROM @RESULTS_TABLE
	
		SET @RELEASECURSOR = CURSOR FOR SELECT LOT_NUMBER, REL_NUMBER FROM @RESULTS
		OPEN @RELEASECURSOR
		FETCH NEXT FROM @RELEASECURSOR INTO @LOT_NUMBER, @REL_NUMBER
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			SET @CHILDCURSOR = CURSOR FOR 
				SELECT WAFER_ID FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART 
				WHERE ARLT.ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID AND ASSEMBLY_RELEASE_NO = @REL_NUMBER AND LOT_NUMBER = @LOT_NUMBER
				
			SET @FINAL_WAFER_ID = ''
			OPEN @CHILDCURSOR
			FETCH NEXT FROM @CHILDCURSOR INTO @LOT_WAFER_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @FINAL_WAFER_ID = ISNULL(@FINAL_WAFER_ID,'') + @LOT_WAFER_ID + ';'
			
				SET @LOT_WAFER_ID = NULL
				FETCH NEXT FROM @CHILDCURSOR INTO @LOT_WAFER_ID
			END
			CLOSE @CHILDCURSOR
 			DEALLOCATE @CHILDCURSOR

			IF( LEN( LTRIM( RTRIM( @FINAL_WAFER_ID ) ) ) > 1 )
			BEGIN
				SET @FINAL_WAFER_ID = SUBSTRING(@FINAL_WAFER_ID, 1, LEN( LTRIM( RTRIM( @FINAL_WAFER_ID ) ) ) - 1)
			END

			SELECT @ASSM_QTY_OUT  = SUM( SHIP_QTY ), @ASSM_DATE_OUT = ISNULL( MAX(SL.SHIP_DATE), MAX(SL.TIMESTAMP) ) 
			FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.LOT_ID = LT.LOT_ID AND STATUS = 'TRANSIT' AND LT.LOT_NUMBER = @REL_NUMBER
								
			UPDATE @RESULTS SET LOT_WAFER_ID = @FINAL_WAFER_ID, QTY_OUT = @ASSM_QTY_OUT, DATE_OUT = @ASSM_DATE_OUT WHERE REL_NUMBER = @REL_NUMBER AND LOT_NUMBER = @LOT_NUMBER
		
			SET @LOT_NUMBER = NULL
			SET @REL_NUMBER = NULL
			SET @LOT_WAFER_ID = NULL
			SET @ASSM_QTY_OUT = NULL
			SET @ASSM_DATE_OUT = NULL 
			FETCH NEXT FROM @RELEASECURSOR INTO @LOT_NUMBER, @REL_NUMBER
	 	END
	  	CLOSE @RELEASECURSOR
	 	DEALLOCATE @RELEASECURSOR

		SELECT * FROM @RESULTS ORDER BY PS_TYPE_ID, REL_NUMBER, REL_DATE DESC
	END
END